from Common.Singleton import *

from DataAccess.blp_client import *
from DataAccess.DBConnFactory import *

from Misc.Utils import *
from string import Template

class FXRegister(Singleton):

	def	import_fx(self, ref_date, for_curr, dom_curr):
	
		if not hasattr(self, "fx_register"):
			setattr(self, "fx_register", {})
		
		if not self.fx_register.has_key((ref_date, for_curr, dom_curr)):
			self.fx_register[(ref_date, for_curr, dom_curr)] = query_fx_quote(ref_date, for_curr, dom_curr)
	
	def get_quote(self, ref_date, for_curr, dom_curr):
		
		if for_curr == dom_curr:
			return 1.0
		else:
			self.import_fx(ref_date, for_curr, dom_curr)
			return self.fx_register[(ref_date, for_curr, dom_curr)]

def query_fx_quote(ref_date, for_curr, dom_curr):

	fx = for_curr + dom_curr + ' Curncy'
	r = query_blp_data([fx], ['PX_LAST'], ref_date, ref_date)
	if r:
		return r[fx]['PX_LAST']
	else:
		print '      err info...failed in querying fx quote,', fx, ref_date.isoformat()

		
class QuoteRegister(Singleton):

	def	import_stock_quote(self, start_date, end_date, tick_list = None, exch_list = ['CNSESH', 'CNSESZ']):
	
		if not hasattr(self, "stock_quote_register"):
			setattr(self, "stock_quote_register", {})
			
		self.stock_quote_register = query_stock_block_quote(start_date, end_date, tick_list, exch_list)
		
		print '      running info...stock quote import completed'

	def	import_index_quote(self, start_date, end_date, tick_list = None, exch_list = ['CNSESH', 'CNSESZ']):
	
		if not hasattr(self, "index_quote_register"):
			setattr(self, "index_quote_register", {})
			
		self.index_quote_register = query_index_block_quote(start_date, end_date, tick_list, exch_list)
		
		print '      running info...index quote import completed'
		
	def get_stock_quote(self, ref_date, ticker, quote_type):
	
		if not hasattr(self, "stock_quote_register"):
		
			print '      war info...stock quote register is not initialized'
			
			#instead quering from database directly
			quote = query_stock_day_quote(ref_date, [ticker])
			
			if quote and quote.has_key(ticker):
				return quote[ticker][quote_type]
			else:
				print '      war info...quering stock quote failed,', ticker, ref_date.isoformat()
			
		if self.stock_quote_register and self.stock_quote_register.has_key(ticker):
		
			quote = self.stock_quote_register[ticker]
			
			if quote.has_key(ref_date):
				return quote[ref_date][quote_type]
			else: #get latest
				pre_days = [d for d in quote.keys() if d <= ref_date]
				if pre_days:
					return quote[max(pre_days)][quote_type]
				else:
					print '      war info...quering stock quote failed,', ticker, ref_date.isoformat()

	def get_index_quote(self, ref_date, ticker, quote_type):
	
		if not hasattr(self, "index_quote_register"):
		
			print '      war info...index quote register is not initialized'
			
			#instead quering from database directly
			quote = query_index_day_quote(ref_date, [ticker])
			
			if quote and quote.has_key(ticker):
				return quote[ticker][quote_type]
			else:
				print '      war info...quering index quote failed,', ticker, ref_date.isoformat()
			
		if self.index_quote_register and self.index_quote_register.has_key(ticker):
		
			quote = self.index_quote_register[ticker]
			
			if quote.has_key(ref_date):
				return quote[ref_date][quote_type]
			else: #get latest
				pre_days = [d for d in quote.keys() if d <= ref_date]
				if pre_days:
					return quote[max(pre_days)][quote_type]
				else:
					print '      war info...quering index quote failed,', ticker, ref_date.isoformat()
				
def query_stock_block_quote(start_date, end_date, tick_list = None, exch_list = ['CNSESH', 'CNSESZ']):
	
	sql_tpl = Template('''select quote.SYMBOL, TO_DATE(TDATE,'yyyymmdd'), TOPEN, TCLOSE, HIGH, LOW, AVGPRICE
							from CHDQUOTE quote
							${TICK_CRITERIA}
							and TDATE >= '${START}'
							and TDATE <= '${END}'
							and VOTURNOVER > 0
							and ${EXCH_CRITERIA}''')
						   
	sql_text = sql_tpl.substitute(START = start_date.strftime('%Y%m%d'),
									END = end_date.strftime('%Y%m%d'),
									TICK_CRITERIA = format_sql_tick_criteria('quote.SYMBOL', tick_list),
									EXCH_CRITERIA = format_sql_criteria('EXCHANGE', exch_list))
	cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()								  
	cursor.execute(sql_text)
	r = cursor.fetchall()
	
	quote_dict = {}
	for elem in r:
	
		if quote_dict.has_key(elem[0]):
			quote_dict[elem[0]][elem[1].date()] = (elem[2], elem[3], elem[4], elem[5], elem[6])
		else:
			quote_dict[elem[0]] = {elem[1].date() : (elem[2], elem[3], elem[4], elem[5], elem[6])}
			
	return quote_dict
	
def query_index_block_quote(start_date, end_date, tick_list = None, exch_list = ['CNSESH', 'CNSESZ']):
	
	sql_tpl = Template('''select quote.SYMBOL, TO_DATE(TDATE,'yyyymmdd'), TOPEN, TCLOSE, HIGH, LOW
							from CIHDQUOTE quote
							${TICK_CRITERIA}
							and TDATE >= '${START}'
							and TDATE <= '${END}'
							and VOTURNOVER > 0
							and ${EXCH_CRITERIA}''')
						   
	sql_text = sql_tpl.substitute(START = start_date.strftime('%Y%m%d'),
									END = end_date.strftime('%Y%m%d'),
									TICK_CRITERIA = format_sql_tick_criteria('quote.SYMBOL', tick_list),
									EXCH_CRITERIA = format_sql_criteria('EXCHANGE', exch_list))
	cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()								  
	cursor.execute(sql_text)
	r = cursor.fetchall()
	
	quote_dict = {}
	for elem in r:
	
		if quote_dict.has_key(elem[0]):
			quote_dict[elem[0]][elem[1].date()] = (elem[2], elem[3], elem[4], elem[5])
		else:
			quote_dict[elem[0]] = {elem[1].date() : (elem[2], elem[3], elem[4], elem[5])}
			
	return quote_dict
				
def query_stock_day_quote(ref_date, tick_list = None, exch_list = ['CNSESH', 'CNSESZ']):

	sql_tpl = Template('''select quote.SYMBOL, TOPEN, TCLOSE, HIGH, LOW, AVGPRICE
						  from CHDQUOTE quote,
							   (select q.SYMBOL as SYMBOL, max(q.TDATE) as d
								  from CHDQUOTE q
								   ${TICK_CRITERIA}
								   and q.VOTURNOVER > 0
								   and ${EXCH_CRITERIA}
								   and q.TDATE <= '${DATE}'
								 group by q.SYMBOL) ld
						 where quote.SYMBOL = ld.SYMBOL
						   and quote.TDATE = ld.d''')
						   
	sql_text = sql_tpl.substitute(DATE = ref_date.strftime('%Y%m%d'),
									EXCH_CRITERIA = format_sql_criteria('EXCHANGE', exch_list),
									TICK_CRITERIA = format_sql_tick_criteria('q.SYMBOL', tick_list))
	cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()								  
	cursor.execute(sql_text)
	r = cursor.fetchall()
	
	quote_dict = {}
	for elem in r:
		quote_dict[elem[0]] = (elem[1], elem[2], elem[3], elem[4], elem[5])
		
	return quote_dict
		
def query_index_day_quote(ref_date, tick_list = None, exch_list = ['CNSESH', 'CNSESZ']):
		
	sql_tpl = Template('''select quote.SYMBOL, TOPEN, TCLOSE, HIGH, LOW
						  from CIHDQUOTE quote 
						  ${TICK_CRITERIA} 
						  and ${EXCH_CRITERIA} 
						  and TDATE = '${DATE}' ''')
						   
	sql_text = sql_tpl.substitute(DATE = ref_date.strftime('%Y%m%d'),
									EXCH_CRITERIA = format_sql_criteria('EXCHANGE', exch_list),
									TICK_CRITERIA = format_sql_tick_criteria('quote.SYMBOL', tick_list))
									
	cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()
	cursor.execute(sql_text)
	r = cursor.fetchall()
	
	quote_dict = {}
	for elem in r:
		quote_dict[elem[0]] = (elem[1], elem[2], elem[3], elem[4])
		
	return quote_dict
		
def query_futures_quote(ref_date, tick_list = None, exch_list = None):

	pass #to be implemented
	